// ==================================================================================================
// Copyright(C) 2018 FI Ltd. All rights reserved.
//
// SYSTEM NAME	   : FI.Net Application Framework
// COMPONENT ID	   : FI.Helpers.Data.DbHelper
// COMPONENT DESC  :		
//
// CREATED DATE/BY :	18 Aug 2018 / Tian Mi
//
// REVISION HISTORY:	DATE/BY			SR#/CS/PM#/OTHERS		DESCRIPTION OF CHANGE
//                      11 Dec 2018/He Jian Bo                  remove the method "ClearParameterCache()"     
// ==================================================================================================

using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Diagnostics.CodeAnalysis;
using System.Reflection;
using System.Text.RegularExpressions;

using FI.Core.ApplicationContexts;
using FI.Helpers.Data.Properties;

using FI.Helpers.Data.Configuration;
using FI.Core.Utilities;
using FI.Core.Data;
using FI.Core.IoC;
using FI.Core.Transactions;


namespace FI.Helpers.Data
{
    /// <summary>
    /// Help class for data access.
    /// </summary>
    public class DbHelper
    {
        #region Variable

        /// <summary>
        /// The placeholder for field name.
        /// </summary>
        public const string FieldNameFormat = "{DataColumnName}";

        /// <summary>
        /// Table and field name format.
        /// </summary>
        public const string TableNameFormat = "{TableName}";

        private const string  DATASET  ="dataSet";

        private const string DATATABLE = "dataTable";

        /// <summary>
        /// Parameter cache, provide by Enterprise Library.
        /// </summary>
        private static readonly IParameterCache parameterCache = ServiceLocatorFactory.GetServiceLocator().GetService<IParameterCache>();

        /// <summary>
        /// Database intance.
        /// </summary>
        private readonly Database database;

        private readonly string databaseName = string.Empty;

        /// <summary>
        /// Whether the parameter format in config file is checked.
        /// </summary>
        private static bool parameterFormatChecked;

        private DataAccessSettings dataAccessSettings = null;


        #endregion

        internal DbHelper(string databaseName, DataAccessSettings dataAccessSettings)
            : this(databaseName)
        {
            Guard.ArgumentNotNull(dataAccessSettings, "dataAccessSettings");
            this.dataAccessSettings = dataAccessSettings;
        }

        #region Construction

        ///// <summary>
        ///// Initializes a new instance of the <see cref="DbHelper"/> class.
        ///// </summary>
        //public DbHelper()
        //        : this(string.Empty)
        //{
        //}

        /// <summary>
        /// Initializes a new instance of the <see cref="DbHelper"/> class.
        /// </summary>
        /// <param name="databaseName">Specify database name</param>
        public DbHelper(string databaseName = "")
        {
            if (!string.IsNullOrEmpty(databaseName))
            {
                this.databaseName = databaseName;
                this.database = DatabaseFactories.GetDatabase(databaseName);
            }
            else
            {
                this.database = DatabaseFactories.GetDatabase();
                this.databaseName = this.database.DatabaseName;
            }
        }

        #endregion

        #region Property

        /// <summary>
        /// Retrieve the database instance
        /// </summary>
        /// <value>The database.</value>
        public Database Database
        {
            get
            {
                return database;
            }
        }

        /// <summary>
        /// Gets dbTransaction ojbect from Context.
        /// </summary>
        /// <value>The dbTransaction instance</value>
        [SuppressMessage("Microsoft.Naming", "CA1706:ShortAcronymsShouldBeUppercase", MessageId = "Member")]
        public DbTransaction DbTransaction
        {
            get
            {
                return Transaction.GetDbTransaction(databaseName);
            }
        }

        #endregion

        #region Event & Event handler.

        /// <summary>
        /// Provides event to retrieve data rows, transaction id and user id when the DataTable is updating.
        /// </summary>
        public event EventHandler<DataUpdatingEventArgs> DataUpdating;

        /// <summary>
        /// Provides event to retrieve data rows, transaction id and user id when the DataTable is updated.
        /// </summary>
        public event EventHandler<DataUpdatedEventArgs> DataUpdated;

        /// <summary>
        /// Provides event to retrieve the intance of CommandList instance when it be created.
        /// </summary>
        public event EventHandler<CommandListCreatedEventArgs> CommandListCreated;

        #endregion

        #region Execute method.

        /// <summary>
        /// Executes the input DbCommand, return the value of the first column first row.
        /// </summary>
        /// <param name="dbCommand">The DbCommand instance to be executed</param>
        /// <returns>
        /// The value of the first column of the first row in the result.
        /// </returns>
        public object ExecuteScalar(DbCommand dbCommand)
        {
            return
                    (DbTransaction == null)
                            ? database.ExecuteScalar(dbCommand)
                            : database.ExecuteScalar(dbCommand, DbTransaction);
        }

        /// <summary>
        /// Executes the stored procedure with input parameter values, return the value of the first column first row.
        /// </summary>
        /// <param name="storedProcedureName">Stored procedure name</param>
        /// <param name="parameterValues">Array list of parameter values</param>
        /// <returns>
        /// The value of the first column of the first row in the result.
        /// </returns>
        public object ExecuteScalar(string storedProcedureName, params object[] parameterValues)
        {
            DbCommand dbCommand = BuildDbCommand(storedProcedureName);
            AssignParameterValues(dbCommand, parameterValues);

            return ExecuteScalar(dbCommand);
        }

        /// <summary>
        /// Executes the stored procedure with input parameter values, return the value of the first column first row with specify data type.
        /// If the return value is DBNull, this method will return the default value of specify data type.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="storedProcedureName">Stored procedure name</param>
        /// <param name="parameterValues">Array list of parameter values</param>
        /// <returns>
        /// The value of the first column of the first row in the result.
        /// </returns>
        /// <example>
        /// 	<code>
        /// dbHelper.ExecuteScalar&lt;int&gt;("P_UNITTEST_PARENT_SELECT", 1)
        /// </code>
        /// </example>
        public T ExecuteScalar<T>(string storedProcedureName, params object[] parameterValues)
        {
            object returnValue = ExecuteScalar(storedProcedureName, parameterValues);

            return (returnValue == null || returnValue == DBNull.Value) ? default(T) : (T) returnValue;
        }

        /// <summary>
        /// Executes stored procedure and returns the number of rows affected.
        /// </summary>
        /// <param name="storedProcedureName">Stored procedure name.</param>
        /// <param name="parameterValues">Array list of parameter values</param>
        /// <returns>The number of rows affected.</returns>
        public int ExecuteNonQuery(string storedProcedureName, params object[] parameterValues)
        {
            DbCommand dbCommand = BuildDbCommand(storedProcedureName);
            AssignParameterValues(dbCommand, parameterValues);

            return ExecuteNonQuery(dbCommand);
        }

        /// <summary>
        /// Executes DbCommand and returns the number of rows affected.
        /// </summary>
        /// <param name="dbCommand">The DbCommand be executed</param>
        /// <returns>The number of rows affected.</returns>
        public int ExecuteNonQuery(DbCommand dbCommand)
        {
            return
                    (DbTransaction == null)
                            ? database.ExecuteNonQuery(dbCommand)
                            : database.ExecuteNonQuery(dbCommand, DbTransaction);
        }

        #endregion

        #region Fill method.

        /// <summary>
        /// Creates a DbCommend instance for input stored procedure name.
        /// </summary>
        /// <param name="storedProcedureName">The name of stored procedure which used to build the DbCommand</param>
        /// <returns>
        /// A DbCommand instance build for the input stored procedure name.
        /// </returns>
        [SuppressMessage("Microsoft.Naming", "CA1706:ShortAcronymsShouldBeUppercase", MessageId = "Member")]
        public DbCommand BuildDbCommand(string storedProcedureName)
        {
            DbCommand dbCommand = database.GetStoredProcCommand(storedProcedureName);
            dbCommand.CommandTimeout = DataAccessConfigurationView.GetCommondTimeout(dataAccessSettings);
            parameterCache.SetParameters(dbCommand, database);

            return dbCommand;
        }

        /// <summary>
        /// Builds a CommandList instance for fill method with input DataSet and stored procedure names.
        /// If the related stored procedure name not include in the dictionary, this method will generate the default stored procedure name base on the configuration.
        /// </summary>
        /// <param name="dataSet">The DataSet used to create CommandList</param>
        /// <param name="storedProcedureNames">The stored procedure name dictionary for DataSet</param>
        /// <returns>A CommandList instance for fill method.</returns>
        /// <exception cref="ArgumentNullException">Input DataSet is null</exception>
        public CommandList BuildFillCommandList(DataSet dataSet, Dictionary<string, string> storedProcedureNames)
        {
            if (dataSet == null)
            {
                throw new ArgumentNullException(DATASET);
            }

            CommandList commandList = new CommandList();

            foreach (DataTable dataTable in dataSet.Tables)
            {
                string storedProcedureName;

                if (!storedProcedureNames.TryGetValue(dataTable.TableName, out storedProcedureName))
                {
                    storedProcedureName = GetProcedureName(dataTable, DbOperationType.Select, dataAccessSettings);
                }

                Commands commands = new Commands();

                commands.SelectCommand = database.GetStoredProcCommand(storedProcedureName);
                commands.SelectCommand.CommandTimeout = DataAccessConfigurationView.GetCommondTimeout(dataAccessSettings);
                parameterCache.SetParameters(commands.SelectCommand, database);

                commandList.Add(dataTable.TableName, commands);
            }

            return commandList;
        }

        /// <summary>
        /// Executes the DbCommand instances in input CommandList, and fills the input DataSet with retrieved records.
        /// </summary>
        /// <param name="dataSet">The DataSet need to be filled</param>
        /// <param name="commandList">The CommandList used to be execute.</param>
        /// <exception cref="ArgumentNullException">Input DataSet is null</exception>
        public void Fill(DataSet dataSet, CommandList commandList)
        {
            if (dataSet == null)
            {
                throw new ArgumentNullException(DATASET);
            }

            bool isEnforceConstraints = dataSet.EnforceConstraints;
            dataSet.EnforceConstraints = false;

            foreach (DataTable dataTable in dataSet.Tables)
            {
                Fill(dataTable, commandList[dataTable.TableName].SelectCommand);
            }

            dataSet.EnforceConstraints = isEnforceConstraints;
        }

        /// <summary>
        /// Executes the DbCommand, and fills the input DataTable with retrieved records.
        /// </summary>
        /// <param name="dataTable">The DataTable need to be filled</param>
        /// <param name="dbCommand">The dbCommand used to be executed</param>
        /// <exception cref="ArgumentNullException">Input DataTable is null</exception>
        public void Fill(DataTable dataTable, DbCommand dbCommand)
        {
            if (dataTable == null)
            {
                throw new ArgumentNullException(DATATABLE);
            }

            bool noDataSet = FillDataSetForDataTable(dataTable);

            DbTransaction dbTransaction = DbTransaction;
            if (dbTransaction == null)
            {
                database.LoadDataSet(dbCommand, dataTable.DataSet, dataTable.TableName);
            }
            else
            {
                database.LoadDataSet(dbCommand, dataTable.DataSet, dataTable.TableName, dbTransaction);
            }

            if (noDataSet)
            {
                dataTable.DataSet.Tables.Remove(dataTable);
            }
        }

        /// <summary>
        /// Execute the stored procedure, and retrieve records to fill the input DataSet.
        /// </summary>
        /// <param name="dataSet">The DataSet need to be fill</param>
        /// <param name="tableNames">The DataTable name list which need to be fill in DataSet</param>
        /// <param name="storedProcedureName">Stored procedure name</param>
        /// <param name="parameterValues">Parameter values of stored procedure</param>
        /// <exception cref="ArgumentNullException">Input DataSet is null</exception>
        public void Fill(DataSet dataSet, string[] tableNames, string storedProcedureName,
                         params object[] parameterValues)
        {
            if (dataSet == null)
            {
                throw new ArgumentNullException(DATASET);
            }

            DbCommand dbCommand = BuildDbCommand(storedProcedureName);
            AssignParameterValues(dbCommand, parameterValues);

            Fill(dataSet, tableNames, dbCommand);
        }

        /// <summary>
        /// Execute the DbCommand, and retrieve records to fill the input DataSet.
        /// </summary>
        /// <param name="dataSet">The DataSet need to be fill</param>
        /// <param name="tableNames">The DataTable name list which need to be fill in DataSet</param>
        /// <param name="dbCommand">DbCommand instance</param>
        /// <exception cref="ArgumentNullException">Input DataSet is null</exception>
        public void Fill(DataSet dataSet, string[] tableNames, DbCommand dbCommand)
        {
            if (dataSet == null)
            {
                throw new ArgumentNullException(DATASET);
            }

            DbTransaction dbTransaction = DbTransaction;

            if (dbTransaction == null)
            {
                database.LoadDataSet(dbCommand, dataSet, tableNames);
            }
            else
            {
                database.LoadDataSet(dbCommand, dataSet, tableNames, dbTransaction);
            }
        }

        /// <summary>
        /// Executes the store procedure, and fills the input DataTable with retreive records.
        /// </summary>
        /// <param name="dataTable">The DataTable need to be filled</param>
        /// <param name="storedProcedureName">The stored procedure name</param>
        /// <param name="parameterValues">The parameter values of stored procedure</param>
        /// <exception cref="ArgumentNullException">Input DataTable is null</exception>
        public void Fill(DataTable dataTable, string storedProcedureName, params object[] parameterValues)
        {
            if (dataTable == null)
            {
                throw new ArgumentNullException(DATATABLE);
            }

            DbCommand dbCommand = BuildDbCommand(storedProcedureName);
            AssignParameterValues(dbCommand, parameterValues);

            Fill(dataTable, dbCommand);
        }

        #endregion

        #region Update method.

        /// <summary>
        /// Builds CommandList instance for the input DataSet.
        /// The DbCommand instances for Insert/Update/Delete operations will be build base on the row status of rows in DataTable.
        /// </summary>
        /// <param name="dataSet">DataSet instance which used to build CommandList</param>
        /// <returns>
        /// CommandList instance which genereated base on the input DataSet.
        /// </returns>
        /// <exception cref="ArgumentNullException">Input DataSet is null.</exception>
        public CommandList BuildUpdateCommandList(DataSet dataSet)
        {
            if (dataSet == null)
            {
                throw new ArgumentNullException(DATASET);
            }

            DataSetHelper.CheckDataSet(dataSet);

            CommandList commandList = new CommandList();

            foreach (DataTable dataTable in dataSet.Tables)
            {
                AddUpdateCommand(commandList, dataTable);
            }

            return commandList;
        }

        /// <summary>
        /// Builds CommandList instance for the input DataTable.
        /// The DbCommand instances for Insert/Update/Delete operations will be build base on the row status of rows in DataTable.
        /// </summary>
        /// <param name="dataTable">DataTable instance which used to build CommandList</param>
        /// <returns>
        /// CommandList instance which genereated base on the input DataTable.
        /// </returns>
        /// <exception cref="ArgumentNullException">Input DataTable is null.</exception>
        public CommandList BuildUpdateCommandList(DataTable dataTable)
        {
            if (dataTable == null)
            {
                throw new ArgumentNullException(DATATABLE);
            }

            DataSetHelper.CheckDataTable(dataTable);

            CommandList commandList = new CommandList();

            AddUpdateCommand(commandList, dataTable);

            return commandList;
        }

        /// <summary>
        /// Builds DbCommand instances for Insert/Update/Delete operations and adds into input CommandList instance.
        /// The DbCommand instace will be build base the the row status of rows in input DataTable.
        /// </summary>
        /// <param name="commandList">CommandList instance which will contain the DbCommand.</param>
        /// <param name="dataTable">DataTable instance which used to build DbCommand and add into CommandList</param>
        private void AddUpdateCommand(CommandList commandList, DataTable dataTable)
        {
            Commands commands = new Commands();

            if (dataTable.Select(null, null, DataViewRowState.Deleted).Length > 0)
            {
                string storedProcedureName = GetProcedureName(dataTable, DbOperationType.Delete, dataAccessSettings);
                commands.DeleteCommand = database.GetStoredProcCommand(storedProcedureName);
                commands.DeleteCommand.CommandTimeout = DataAccessConfigurationView.GetCommondTimeout(dataAccessSettings);
                parameterCache.SetParameters(commands.DeleteCommand, database);
                BindParameterSource(dataTable, commands.DeleteCommand.Parameters);
            }

            if (dataTable.Select(null, null, DataViewRowState.ModifiedCurrent).Length > 0)
            {
                string storedProcedureName = GetProcedureName(dataTable, DbOperationType.Update, dataAccessSettings);
                commands.UpdateCommand = database.GetStoredProcCommand(storedProcedureName);
                commands.UpdateCommand.CommandTimeout = DataAccessConfigurationView.GetCommondTimeout(dataAccessSettings);
                parameterCache.SetParameters(commands.UpdateCommand, database);
                BindParameterSource(dataTable, commands.UpdateCommand.Parameters);
            }

            if (dataTable.Select(null, null, DataViewRowState.Added).Length > 0)
            {
                string storedProcedureName = GetProcedureName(dataTable, DbOperationType.Insert, dataAccessSettings);
                commands.InsertCommand = database.GetStoredProcCommand(storedProcedureName);
                commands.InsertCommand.CommandTimeout = DataAccessConfigurationView.GetCommondTimeout(dataAccessSettings);
                parameterCache.SetParameters(commands.InsertCommand, database);
                BindParameterSource(dataTable, commands.InsertCommand.Parameters);
            }

            commandList.Add(dataTable.TableName, commands);
        }

        /// <summary>
        /// Updates the changes in DataSet (Insert/Update/Delete) to database.
        /// </summary>
        /// <param name="dataSet">The DataSet need to be updated</param>
        /// <param name="updateBatchSize">Whether need to use batch  update</param>>
        /// <returns>The number of rows affected.</returns>
        public int Update(DataSet dataSet, int updateBatchSize = 1)
        {
            return Update(dataSet, true, updateBatchSize);
        }

        /// <summary>
        /// Updates the changes in DataSet (Insert/Update/Delete) to database.
        /// </summary>
        /// <param name="dataSet">The DataSet need to be updated</param>
        /// <param name="acceptChangesDuringUpdate">Whether accept changes on DataSet after updated</param>
        /// <param name="updateBatchSize">Whether need to use batch  update</param>>
        /// <returns>The number of rows affected.</returns>
        public int Update(DataSet dataSet, bool acceptChangesDuringUpdate,int updateBatchSize=1)
        {
            if (dataSet == null)
            {
                throw new ArgumentNullException(DATASET);
            }

            CommandList commandList = BuildUpdateCommandList(dataSet);

            if (CommandListCreated != null)
            {
                CommandListCreated(this, new CommandListCreatedEventArgs(commandList));
            }

            return Update(dataSet, null, commandList, acceptChangesDuringUpdate, updateBatchSize);
        }

        /// <summary>
        /// Updates the changes in DataSet (Insert/Update/Delete) to database.
        /// </summary>
        /// <param name="dataSet">The DataSet need to be updated</param>
        /// <param name="commandList">CommandList used to update the DataSet</param>
        /// <param name="updateBatchSize">Whether need to use batch  update</param>>
        /// <returns>The number of rows affected.</returns>
        public int Update(DataSet dataSet, CommandList commandList, int updateBatchSize=1)
        {
            return Update(dataSet, null, commandList, true, updateBatchSize);
        }

        /// <summary>
        /// Updates the changes in DataSet (Insert/Update/Delete) to database.
        /// </summary>
        /// <param name="dataSet">The DataSet need to be updated</param>
        /// <param name="commandList">CommandList used to update the DataSet</param>
        /// <param name="acceptChangesAfterUpdated">Whether accept changes on DataSet after updated</param>
        /// <param name="updateBatchSize">Whether need to use batch  update</param>>
        /// <returns>The number of rows affected.</returns>
        public int Update(DataSet dataSet, CommandList commandList, bool acceptChangesAfterUpdated, int updateBatchSize = 1)
        {
            return Update(dataSet, null, commandList, acceptChangesAfterUpdated, updateBatchSize);
        }

        /// <summary>
        /// Updates the changes in DataTable (Insert/Update/Delete) to database.
        /// </summary>
        /// <param name="dataTable">Input DataTable</param>
        /// <param name="updateBatchSize">Whether need to use batch  update</param>>
        /// <returns>The number of rows affected.</returns>
        public int Update(DataTable dataTable, int updateBatchSize=1)
        {
            return Update(dataTable, true, updateBatchSize);
        }

        /// <summary>
        /// Updates the changes in DataTable (Insert/Update/Delete) to database.
        /// </summary>
        /// <param name="dataTable">The DataTable need to be updated</param>
        /// <param name="acceptChangesAfterUpdated">Whether accept changes on DataTable after updated</param>
        /// <param name="updateBatchSize">Whether need to use batch  update</param>>
        /// <returns>The number of rows affected.</returns>
        public int Update(DataTable dataTable, bool acceptChangesAfterUpdated, int updateBatchSize=1)
        {
            if (dataTable == null)
            {
                throw new ArgumentNullException(DATATABLE);
            }

            CommandList commandList = BuildUpdateCommandList(dataTable);

            if (CommandListCreated != null)
            {
                CommandListCreated(this, new CommandListCreatedEventArgs(commandList));
            }

            bool noDataSet = FillDataSetForDataTable(dataTable);

            int updatedRowCount =
                    Update(dataTable.DataSet, new string[] { dataTable.TableName }, commandList, acceptChangesAfterUpdated, updateBatchSize);

            if (noDataSet)
            {
                dataTable.DataSet.Tables.Remove(dataTable);
            }

            return updatedRowCount;
        }

        private static bool FillDataSetForDataTable(DataTable dataTable)
        {
            bool noDataSet = (dataTable.DataSet == null);
            if (noDataSet)
            {
                new DataSet().Tables.Add(dataTable);
            }
            return noDataSet;
        }

        /// <summary>
        /// Updates the changes in DataTable (Insert/Update/Delete) to database.
        /// </summary>
        /// <param name="dataTable">The DataTable need to be updated</param>
        /// <param name="commandList">CommandList used to update the DataTable</param>
        /// <param name="updateBatchSize">Whether need to use batch  update</param>>
        /// <returns>The number of rows affected.</returns>
        public int Update(DataTable dataTable, CommandList commandList,int updateBatchSize=1)
        {
            return Update(dataTable, commandList, true, updateBatchSize);
        }

        /// <summary>
        /// Updates the changes in DataTable (Insert/Update/Delete) to database.
        /// </summary>
        /// <param name="dataTable">The DataTable need to be updated</param>
        /// <param name="commandList">CommandList used to update the DataTable</param>
        /// <param name="acceptChangesAfterUpdated">Whether accept changes on DataTable after updated</param>
        /// <param name="updateBatchSize">Whether need to use batch  update</param>>
        /// <returns>The number of rows affected.</returns>
        public int Update(DataTable dataTable, CommandList commandList, bool acceptChangesAfterUpdated, int updateBatchSize = 1)
        {
            if (dataTable == null)
            {
                throw new ArgumentNullException(DATATABLE);
            }

            bool noDataSet = FillDataSetForDataTable(dataTable);

            int updatedRowCount =
                    Update(dataTable.DataSet, new string[] { dataTable.TableName }, commandList, acceptChangesAfterUpdated, updateBatchSize);

            if (noDataSet)
            {
                dataTable.DataSet.Tables.Remove(dataTable);
            }

            return updatedRowCount;
        }

        /// <summary>
        /// Updates the specified data set.
        /// </summary>
        /// <param name="dataSet">The data set.</param>
        /// <param name="sortedTableNames">The sorted table names.</param>
        /// <param name="acceptChangesAfterUpdated">if set to <c>true</c> [accept changes after updated].</param>
        /// <param name="updateBatchSize">Whether need to use batch  update</param>>
        /// <returns></returns>
        public int Update(DataSet dataSet, string[] sortedTableNames, bool acceptChangesAfterUpdated, int updateBatchSize=1)
        {
            if (dataSet == null)
            {
                throw new ArgumentNullException(DATASET);
            }

            CommandList commandList = BuildUpdateCommandList(dataSet);

            if (CommandListCreated != null)
            {
                CommandListCreated(this, new CommandListCreatedEventArgs(commandList));
            }

            return Update(dataSet, sortedTableNames, commandList, acceptChangesAfterUpdated, updateBatchSize);
        }

        /// <summary>
        /// Updates the changes in DataSet (Insert/Update/Delete) to database.
        /// </summary>
        /// <param name="dataSet">Dataset include the changes</param>
        /// <param name="sortedTableNames">The sorted table names.</param>
        /// <param name="commandList">CommandList</param>
        /// <param name="acceptChangesDuringUpdate">Whether accept changes after update to database</param>
        /// <param name="updateBatchSize">Whether need to use batch  update</param>>
        /// <returns>The number of rows affected.</returns>
        public int Update(DataSet dataSet, string[] sortedTableNames, CommandList commandList,bool acceptChangesDuringUpdate,int updateBatchSize=1)
        {
            return Update(dataSet, sortedTableNames, commandList, acceptChangesDuringUpdate, true, updateBatchSize);
        }

        /// <summary>
        /// Updates the changes in DataSet (Insert/Update/Delete) to database.
        /// </summary>
        /// <param name="dataSet">Dataset include the changes</param>
        /// <param name="sortedTableNames">The sorted table names.</param>
        /// <param name="commandList">CommandList</param>
        /// <param name="acceptChangesDuringUpdate">if set to <c>true</c> [accept changes during update].</param>
        /// <param name="UpdateSystemFieldBySystem">if set to <c>true</c> [update system field by system].</param>
        /// <param name="updateBatchSize">Whether need to use batch  update</param>>
        /// <returns>The number of rows affected.</returns>
        public int Update(DataSet dataSet, string[] sortedTableNames, CommandList commandList, bool acceptChangesDuringUpdate, bool UpdateSystemFieldBySystem, int updateBatchSize=1)
        {
            int affectedRowCount = 0;
            string[] tableOrderArray = sortedTableNames ?? DataSetHelper.GetDataTableOrder(dataSet, true);
            DataRow[] dataRows;

            //Do delete operation
            for (int i = tableOrderArray.Length - 1; i >= 0; i--)
            {
                DataTable dataTable = dataSet.Tables[tableOrderArray[i]];
                dataRows = dataTable.Select("", "", DataViewRowState.Deleted);
                affectedRowCount += UpdateRows(dataRows, commandList, DbOperationType.Delete, UpdateSystemFieldBySystem, acceptChangesDuringUpdate, updateBatchSize);
            }

            //Do update and insert operations
            for (int i = 0; i < tableOrderArray.Length; i++)
            {
                DataTable dataTable = dataSet.Tables[tableOrderArray[i]];

                dataRows = dataTable.Select("", "", DataViewRowState.ModifiedCurrent);
                affectedRowCount += UpdateRows(dataRows, commandList, DbOperationType.Update, UpdateSystemFieldBySystem, acceptChangesDuringUpdate, updateBatchSize);

                dataRows = dataTable.Select("", "", DataViewRowState.Added);
                affectedRowCount += UpdateRows(dataRows, commandList, DbOperationType.Insert, UpdateSystemFieldBySystem, acceptChangesDuringUpdate, updateBatchSize);
            }

            return affectedRowCount;
        }

        /// <summary>
        /// Updates the changes specify rows (Inserted/Updated/Deleted) to database.
        /// </summary>
        /// <param name="dataRows">DataRow array to be updated</param>
        /// <param name="commandList">The commandList instance.</param>
        /// <param name="dbOperationType">Specify the operation type also specify the rows need to be updated.</param>
        /// <param name="UpdateSystemFieldBySystem">if set to <c>true</c> [update system field by system].</param>
        /// <param name="acceptChangesDuringUpdate">if set to <c>true</c> [accept changes during update].</param>
        /// <param name="updateBatchSize">Whether need to use batch  update</param>>
        /// <returns>The number of rows affected.</returns>
        private int UpdateRows(DataRow[] dataRows, CommandList commandList, DbOperationType dbOperationType, bool UpdateSystemFieldBySystem,bool acceptChangesDuringUpdate,int updateBatchSize)
        {
            int affectedRowCount = 0;

            if (dataRows.Length > 0)
            {
                Commands commands;
                DbCommand insertCommand;
                DbCommand updateCommand;
                DbCommand deleteCommand;
                DbCommand currentCommand;

                string dataTableName = dataRows[0].Table.TableName;

                if (!commandList.TryGetValue(dataTableName, out commands))
                {
                    throw new NotSupportedException(string.Format(Resources.Culture, Resources.CommandNotFoundForTable, dataTableName));
                }

                currentCommand = insertCommand = updateCommand = deleteCommand = null;

                switch (dbOperationType)
                {
                    case DbOperationType.Insert:
                        currentCommand = insertCommand = commands.InsertCommand;
                        break;
                    case DbOperationType.Update:
                        currentCommand = updateCommand = commands.UpdateCommand;
                        break;
                    case DbOperationType.Delete:
                        currentCommand = deleteCommand = commands.DeleteCommand;
                        break;
                }

                if (currentCommand == null)
                {
                    throw new NotSupportedException(string.Format(Resources.Culture, Resources.CommandNotFound, dbOperationType));
                }

                if (UpdateSystemFieldBySystem)
                {
                    DataSetHelper.UpdateSystemField(dataRows, ApplicationContext.Current.TransactionId, ApplicationContext.Current.UserName, dataAccessSettings);
                }

                if (DataUpdating != null)
                {
                    DataUpdating(this, new DataUpdatingEventArgs(dbOperationType, dataRows));
                }

                if (System.Transactions.Transaction.Current != null)
                {
                    affectedRowCount = database.UpdateDataSet(dataRows, insertCommand, updateCommand, deleteCommand, UpdateBehavior.Standard, acceptChangesDuringUpdate, updateBatchSize);
                }
                else
                {
                    if (DbTransaction != null)
                    {
                        affectedRowCount = database.UpdateDataSet(dataRows, insertCommand, updateCommand, deleteCommand, DbTransaction, acceptChangesDuringUpdate, updateBatchSize);
                    }
                    else
                    {
                        affectedRowCount = database.UpdateDataSet(dataRows, insertCommand, updateCommand, deleteCommand, UpdateBehavior.Transactional, acceptChangesDuringUpdate, updateBatchSize);
                    }
                }

                if (DataUpdated != null)
                {
                    DataUpdated(this, new DataUpdatedEventArgs(dbOperationType, dataRows));
                }
            }

            return affectedRowCount;
        }

        #endregion

        #region Private method.

        /// <summary>
        /// Binds the parameters' to the columns in DataTable
        /// The source column and source version property will be setting base on the naming conventions in configuration file.
        /// </summary>
        /// <param name="dataTable">The DataTable object used to bind the parameters.</param>
        /// <param name="parameters">Parameter collection need to be bind.</param>
        private void BindParameterSource(DataTable dataTable, IDataParameterCollection parameters)
        {
            foreach (IDataParameter parameter in parameters)
            {
                if (parameter.Direction != ParameterDirection.ReturnValue)
                {
                    string columnName = GetColumnName(parameter.ParameterName, dataAccessSettings);
                    if (dataTable.Columns.Contains(columnName))
                    {
                        parameter.SourceColumn = columnName;
                        if (NeedOrignalVersion(parameter.ParameterName,dataAccessSettings))
                        {
                            parameter.SourceVersion = DataRowVersion.Original;
                        }
                        else
                        {
                            parameter.SourceVersion = DataRowVersion.Current;
                        }
                    }
                }
            }
        }
        private static string GetProcedureName(DataTable dataTable, DbOperationType operationType, DataAccessSettings dataAccessSettings = null)
        {
            return GetProcedureName(dataTable.TableName, operationType, dataAccessSettings);
        }

        /// <summary>
        /// Gets the name of the sequence.
        /// </summary>
        /// <param name="tableName">Name of the table.</param>
        /// <param name="dataAccessSettings">The data access settings.</param>
        /// <returns>The sequence name.</returns>
        public static string GetSequenceName(string tableName, DataAccessSettings dataAccessSettings = null)
        {
            string spName = string.Empty;
            string formatName = string.Empty;
            string regexString = "^" + DataAccessConfigurationView.GetDataTableMapping(dataAccessSettings) + "$";
            regexString = regexString.Replace(TableNameFormat, @"(\S*)");

            Regex regex = new Regex(regexString, RegexOptions.IgnoreCase);
            Match match = regex.Match(tableName);
            if (match.Success)
            {
                GroupCollection gc = match.Groups;
                if (gc.Count > 1)
                {
                    formatName = gc[1].Value;
                }
            }

            if (String.IsNullOrEmpty(formatName))
            {
                throw new TableNameMismatchException(
                        string.Format(Resources.Culture, Resources.TableNameUnFit, tableName));
            }

            return string.Format("S_{0}_ID", formatName);
        }

        /// <summary>
        /// Gets the related stored procedure name base on the input DataTable name, operation type and the naming conventions in configuration.
        /// </summary>
        /// <param name="tableName">Name of the table.</param>
        /// <param name="operationType">Type of the operation</param>
        /// <param name="dataAccessSettings">The data access settings.</param>
        /// <returns>The related stored procedure name.</returns>
        public static string GetProcedureName(string tableName, DbOperationType operationType, DataAccessSettings dataAccessSettings = null)
        {
            string spName = string.Empty;
            string formatName = string.Empty;
            string regexString = "^" + DataAccessConfigurationView.GetDataTableMapping(dataAccessSettings) + "$";
            regexString = regexString.Replace(TableNameFormat, @"(\S*)");

            Regex regex = new Regex(regexString, RegexOptions.IgnoreCase);
            Match match = regex.Match(tableName);
            if (match.Success)
            {
                GroupCollection gc = match.Groups;
                if (gc.Count > 1)
                {
                    formatName = gc[1].Value;
                }
            }

            if (String.IsNullOrEmpty(formatName))
            {
                throw new TableNameMismatchException(
                        string.Format(Resources.Culture, Resources.TableNameUnFit, tableName));
            }

            switch (operationType)
            {
                case DbOperationType.Select:
                    {
                        spName = DataAccessConfigurationView.GetSelectProcedureFormat(dataAccessSettings).Replace(TableNameFormat, formatName);
                        break;
                    }
                case DbOperationType.Delete:
                    {
                        spName = DataAccessConfigurationView.GetDeleteProcedureFormat(dataAccessSettings).Replace(TableNameFormat, formatName);
                        break;
                    }
                case DbOperationType.Update:
                    {
                        spName = DataAccessConfigurationView.GetUpdateProcedureFormat(dataAccessSettings).Replace(TableNameFormat, formatName);
                        break;
                    }
                case DbOperationType.Insert:
                    {
                        spName = DataAccessConfigurationView.GetInsertProcedureFormat(dataAccessSettings).Replace(TableNameFormat, formatName);
                        break;
                    }
            }
            return spName;
        }

        /// <summary>
        /// Gets the regex pattern.
        /// </summary>
        /// <param name="currentValue">current value of DataColumn</param>
        /// <param name="dataAccessSettings">The data access settings.</param>
        /// <returns>Regex pattern.</returns>
        private static string GetRegexPattern(bool currentValue, DataAccessSettings dataAccessSettings = null)
        {
            //parameter token for different database. ":"for oracle, "@"for sql server
            string parameterTokenPattern = "^[:@]{0,1}";
            string regexString;

            if (currentValue)
            {
                regexString = parameterTokenPattern + DataAccessConfigurationView.GetCurrentParameterFormat(dataAccessSettings) + "$";
            }
            else
            {
                regexString = parameterTokenPattern + DataAccessConfigurationView.GetOriginalParameterFormat(dataAccessSettings) + "$";
            }
            regexString = regexString.Replace(FieldNameFormat, @"(\S*)");
            return regexString;
        }

        /// <summary>
        /// Gets the column name base on the input parameter name and the configuration.
        /// </summary>
        /// <param name="parameterName">The name of paramter.</param>
        /// <param name="dataAccessSettings">The data access settings.</param>
        /// <returns>
        /// The related column name of input paramter name
        /// </returns>
        public static string GetColumnName(string parameterName, DataAccessSettings dataAccessSettings = null)
        {
            if (!parameterFormatChecked)
            {
                //The parameter format for current value and original value cannot be the same
                if (DataAccessConfigurationView.GetCurrentParameterFormat(dataAccessSettings) ==
                    DataAccessConfigurationView.GetOriginalParameterFormat(dataAccessSettings))
                {
                    throw new ConfigurationErrorsException(string.Format(Resources.Culture, Resources.CanNotBeSame));
                }
                //The parameter format for original value cannot be the same as column name
                if (DataAccessConfigurationView.GetOriginalParameterFormat(dataAccessSettings) == FieldNameFormat)
                {
                    throw new ConfigurationErrorsException(
                            string.Format(Resources.Culture, Resources.InvalidParameterForOriginalValue));
                }
                parameterFormatChecked = true;
            }

            Regex regex;

            string columnName;

            //If the parameter format for current value is equal to field name, match the format for original value first   
            // e.g., o_version_no will match the related format for <add name="Current" format="{DataColumnName}" />
            //[He Jiang Yan comments at 22 Mar 2007]:
            //Why should we do like this?
            //It is due to:
            //if current format ={DataColumnName}, original format = o_{DataColumnName}
            //and parameter=@o_id, it is original value
            //current regex pattern will be @{DataColumnName}
            //so if we check current value first, @o_id will be taken as current value not original value.
            if (DataAccessConfigurationView.GetCurrentParameterFormat(dataAccessSettings) == FieldNameFormat)
            {
                regex = new Regex(GetRegexPattern(false, dataAccessSettings), RegexOptions.IgnoreCase);
                columnName = MatchColumnName(regex, parameterName, dataAccessSettings);
                if (!string.IsNullOrEmpty(columnName))
                {
                    return columnName;
                }

                regex = new Regex(GetRegexPattern(true, dataAccessSettings), RegexOptions.IgnoreCase);
                columnName = MatchColumnName(regex, parameterName, dataAccessSettings);
                if (!string.IsNullOrEmpty(columnName))
                {
                    return columnName;
                }
            }
            else
            {
                regex = new Regex(GetRegexPattern(true, dataAccessSettings), RegexOptions.IgnoreCase);
                columnName = MatchColumnName(regex, parameterName, dataAccessSettings);
                if (!string.IsNullOrEmpty(columnName))
                {
                    return columnName;
                }

                regex = new Regex(GetRegexPattern(false, dataAccessSettings), RegexOptions.IgnoreCase);
                columnName = MatchColumnName(regex, parameterName, dataAccessSettings);
                if (!string.IsNullOrEmpty(columnName))
                {
                    return columnName;
                }
            }
            return string.Empty;
        }

        /// <summary>
        /// Needs the orignal version.
        /// </summary>
        /// <param name="parameterName">Name of the parameter.</param>
        /// <param name="dataAccessSettings">The data access settings.</param>
        /// <returns>Whether or not need orignal version</returns>
        public static bool NeedOrignalVersion(string parameterName, DataAccessSettings dataAccessSettings = null)
        {
            string regexString = GetRegexPattern(false, dataAccessSettings);
            Regex regex = new Regex(regexString, RegexOptions.IgnoreCase);
            Match match = regex.Match(parameterName);
            if (match.Success)
            {
                GroupCollection gc = match.Groups;
                if (gc.Count > 1)
                {
                    return true;
                }
            }
            return false;
        }

        /// <summary>
        /// Matches the name of the column.
        /// </summary>
        /// <param name="regex">The regex.</param>
        /// <param name="parameterName">Name of the parameter.</param>
        /// <param name="dataAccessSettings">The data access settings.</param>
        /// <returns></returns>
        private static string MatchColumnName(Regex regex, string parameterName,DataAccessSettings dataAccessSettings = null)
        {
            Match match = regex.Match(parameterName);
            if (match.Success)
            {
                GroupCollection gc = match.Groups;
                if (gc.Count > 1)
                {
                    return DataAccessConfigurationView.GetDataColumnMapping(dataAccessSettings).Replace(FieldNameFormat, gc[1].Value);
                }
                else
                {
                    return string.Empty;
                }
            }
            else
            {
                return string.Empty;
            }
        }

        #endregion

        #region Methods for parameter.

        ///// <summary>
        ///// Adds a input type parameter to the dbCommand.
        ///// </summary>
        ///// <param name="dbCommand">DbCommand instance</param>        
        ///// <param name="parameterName">Paramter name</param>
        ///// <param name="dbType">DbType</param>
        ///// <param name="value">Parameter value</param>
        //public void AddInParameter(DbCommand dbCommand, string parameterName, DbType dbType, object value)
        //{
        //    if (dbCommand == null)
        //        throw new ArgumentNullException("dbCommand");

        //    if (string.IsNullOrEmpty(parameterName))
        //        throw new ArgumentNullException("parameterName");

        //    if (dbCommand.CommandType == CommandType.Text)
        //        _database.AddInParameter(dbCommand, parameterName, dbType, value);
        //    else
        //        _database.SetParameterValue(dbCommand, parameterName, value);
        //}

        ///// <summary>
        ///// Adds a output parameter to dbCommand.
        ///// </summary>
        ///// <param name="dbCommand">DbCommand instance</param>
        ///// <param name="parameterName">Paramter name</param>
        ///// <param name="dbType">DbType of parameter</param>
        ///// <param name="size">Size of parameter</param>
        ///// <exception cref="ArgumentNullException">Input DataTable is null.</exception>
        //public void AddOutParameter(DbCommand dbCommand, string parameterName, DbType dbType, int size)
        //{
        //    if (dbCommand == null)
        //        throw new ArgumentNullException("dbCommand");

        //    if (string.IsNullOrEmpty(parameterName))
        //        throw new ArgumentNullException("parameterName");

        //    _database.AddOutParameter(dbCommand, parameterName, dbType, size);
        //}

        ///// <summary>
        ///// Add Oracle cursor parameter.
        ///// </summary>
        ///// <param name="dbCommand">DbCommand instance</param>
        ///// <param name="parameterName">Parameter name</param>
        //public void AddOracleCursorParameter(DbCommand dbCommand, string parameterName)
        //{
        //    if (dbCommand == null)
        //        throw new ArgumentNullException("dbCommand");

        //    if (string.IsNullOrEmpty(parameterName))
        //        throw new ArgumentNullException("parameterName");

        //    ((OracleDatabase)_database).AddParameter((OracleCommand)dbCommand, parameterName, OracleType.Cursor, 0, ParameterDirection.Output, true, 0, 0, string.Empty, DataRowVersion.Current, string.Empty);
        //}

        /// <summary>
        /// Assigns values to the input parameters. The order of values is refer the paramters' order.
        /// Output paramters will be assigned a null value.
        /// Inputoutput parameters will be assigned a dbNull.
        /// </summary>
        /// <param name="dbCommand">DbCommand instance</param>
        /// <param name="parameterValues">Values list</param>
        public void AssignParameterValues(DbCommand dbCommand, params object[] parameterValues)
        {
            //ArrayList parameterValueArray;
            //int index = 0;

            //if (dbCommand == null)
            //{
            //    throw new ArgumentNullException("dbCommand");
            //}

            //parameterValueArray = new ArrayList();                                                                                                                                          

            //foreach (DbParameter parameter in dbCommand.Parameters)
            //{
            //    switch (parameter.Direction)
            //    {
            //        case ParameterDirection.Input:
            //            parameterValueArray.Add(parameterValues[index]);
            //            index++;
            //            break;
            //        case ParameterDirection.Output:
            //            parameterValueArray.Add(null);
            //            break;
            //        case ParameterDirection.InputOutput:
            //            parameterValueArray.Add(DBNull.Value);
            //            break;
            //        case ParameterDirection.ReturnValue:
            //            break;
            //    }
            //}
            database.AssignParameterValues(dbCommand, parameterValues);
        }

        /// <summary>
        /// Retrieve the value of DbCommand paramter value.
        /// </summary>
        /// <param name="dbCommand">DbCommand instance</param>
        /// <param name="parameterName">Name of the parameter in DbCommand</param>
        /// <returns>The parameter value.</returns>
        public object GetParameterValue(DbCommand dbCommand, string parameterName)
        {
            if (dbCommand == null)
            {
                throw new ArgumentNullException("dbCommand");
            }

            if (string.IsNullOrEmpty(parameterName))
            {
                throw new ArgumentNullException("parameterName");
            }

            return database.GetParameterValue(dbCommand, parameterName);
        }

        #endregion

        #region Added in iConnect.NET 3.7

        /// <summary>
        /// Assign the parameter values to a <see cref="DbCommand"/>.
        /// </summary>
        /// <param name="command">The <see cref="DbCommand"/> to which the parameter is added to.</param>
        /// <param name="parameters">A dictionary whose entry represents a singel parameter.</param>
        public void AssignParameterValues(DbCommand command, IDictionary<string, object> parameters)
        {        

            if (command == null)
            {
                throw new ArgumentNullException("command");
            }

            if (parameters == null)
            {
                parameters = new Dictionary<string, object>();
            }

            Dictionary<string, object> copiedParams = new Dictionary<string, object>();
            foreach (var parameter in parameters)
            {
                copiedParams.Add(parameter.Key.ToLower(), parameter.Value);
            }

            
            parameterCache.SetParameters(command, this.database);
            foreach (DbParameter parameter in command.Parameters)
            {
                string parameterName = parameter.ParameterName.TrimStart('@').ToLower();
                if (copiedParams.ContainsKey(parameterName))
                {
                    if (parameter.Direction == ParameterDirection.Input)
                    {
                        parameter.Value = copiedParams[parameterName];
                    }
                }
            }
        }

        /// <summary>
        /// Executes the command within a transaction and returns an System.Data.IDataReader through which the result can be read.  It is the responsibility of the caller to close the connection and reader when finished.
        /// </summary>
        /// <param name="command">A <see cref=" DbCommand"/>.</param>
        /// <returns>The created <see cref="IDataReader"/></returns>
        public IDataReader ExecuteReader(DbCommand command)
        {
            if (this.DbTransaction != null)
            {
                return this.database.ExecuteReader(command, this.DbTransaction);
            }

            return this.database.ExecuteReader(command);
        }
        /// <summary>
        /// Executes the command within a transaction and returns an System.Data.IDataReader through which the result can be read.  It is the responsibility of the caller to close the connection and reader when finished.
        /// </summary>
        /// <param name="procedureName">The name of stored procedure.</param>
        /// <param name="parameters">A optional string array including all prameter values.</param>
        /// <returns>The created <see cref="IDataReader"/></returns>
        public IDataReader ExecuteReader(string procedureName, params object[] parameters)
        {
            DbCommand command = this.database.GetStoredProcCommand(procedureName, parameters);
            command.CommandTimeout = DataAccessConfigurationView.GetCommondTimeout(dataAccessSettings);
            return this.ExecuteReader(command);
        }

        /// <summary>
        /// Executes the command within a transaction and returns an System.Data.IDataReader through which the result can be read.  It is the responsibility of the caller to close the connection and reader when finished.
        /// </summary>
        /// <param name="procedureName">The name of stored procedure.</param>
        /// <param name="parameters">A dictionary whose entry represents a singel parameter.</param>
        /// <returns>The created <see cref="IDataReader"/></returns>
        public IDataReader ExecuteReader(string procedureName, IDictionary<string, object> parameters)
        {
            DbCommand command = this.database.GetStoredProcCommand(procedureName);
            command.CommandTimeout = DataAccessConfigurationView.GetCommondTimeout(dataAccessSettings);
            this.AssignParameterValues(command, parameters);
            return this.ExecuteReader(command);
        }
        
        /// <summary>
        /// Executes the command and returns the number of rows affected.
        /// </summary>
        /// <param name="procedureName">The name of stored procedure.</param>
        /// <param name="parameters">A dictionary whose entry represents a singel parameter.</param>
        /// <returns>The number of rows affected.</returns>
        public int ExecuteNonQuery(string procedureName, IDictionary<string, object> parameters)
        {
            if (parameters == null)
            {
                parameters = new Dictionary<string, object>();
            }
            DbCommand command = this.database.GetStoredProcCommand(procedureName);
            command.CommandTimeout = DataAccessConfigurationView.GetCommondTimeout(dataAccessSettings);
            this.AssignParameterValues(command, parameters);
            return this.ExecuteNonQuery(command);
        }

        /// <summary>
        /// Executes the command and returns the first column of the first row in the result set returned by the query. Extra columns or rows are ignored.
        /// </summary>
        /// <param name="procedureName">The name of stored procedure.</param>
        /// <param name="parameters">A dictionary whose entry represents a singel parameter.</param>
        /// <returns>The first column of the first row in the result set.</returns>
        public object ExecuteScalar(string procedureName, IDictionary<string, object> parameters)
        {
            if (parameters == null)
            {
                parameters = new Dictionary<string, object>();
            }
            DbCommand command = this.database.GetStoredProcCommand(procedureName);
            command.CommandTimeout = DataAccessConfigurationView.GetCommondTimeout(dataAccessSettings);
            this.AssignParameterValues(command, parameters);
            return this.ExecuteScalar(command);

            
        }
        /// <summary>
        /// Executes the command and returns the first column of the first row in the result set returned by the query. Extra columns or rows are ignored.
        /// </summary>
        /// <typeparam name="T">The type of return value.</typeparam>
        /// <param name="procedureName">The name of stored procedure.</param>
        /// <param name="parameters">A dictionary whose entry represents a singel parameter.</param>
        /// <returns>The first column of the first row in the result set.</returns>
        public T ExecuteScalar<T>(string procedureName, IDictionary<string, object> parameters)
        {
            if (parameters == null)
            {
                parameters = new Dictionary<string, object>();
            }
            DbCommand command = this.database.GetStoredProcCommand(procedureName);
            command.CommandTimeout = DataAccessConfigurationView.GetCommondTimeout(dataAccessSettings);
            this.AssignParameterValues(command, parameters);
            return (T)this.ExecuteScalar(command);
        }

        /// <summary>
        /// Execute the DbCommand, and retrieve records to fill the input DataSet.
        /// </summary>
        /// <param name="dataSet">The DataSet need to be fill</param>
        /// <param name="tableNames">The DataTable name list which need to be fill in DataSet</param>
        /// <param name="procedureName">The name of stored procedure.</param>
        /// <param name="parameters">A dictionary whose entry represents a singel parameter.</param>
        public void Fill(DataSet dataSet, string[] tableNames, string procedureName, IDictionary<string, object> parameters)
        {
            if (parameters == null)
            {
                parameters = new Dictionary<string, object>();
            }
            DbCommand command = this.database.GetStoredProcCommand(procedureName);
            command.CommandTimeout = DataAccessConfigurationView.GetCommondTimeout(dataAccessSettings);
            this.AssignParameterValues(command, parameters);
            this.Fill(dataSet, tableNames, command);
        }

        /// <summary>
        /// Execute the stored procedure, and retrieve records to fill the input DataSet.
        /// </summary>
        /// <param name="dataTable">The DataTable need to be filled</param>
        /// <param name="procedureName">The name of stored procedure.</param>
        /// <param name="parameters">A dictionary whose entry represents a singel parameter.</param>
        public void Fill(DataTable dataTable, string procedureName, IDictionary<string, object> parameters)
        {
            if (parameters == null)
            {
                parameters = new Dictionary<string, object>();
            }
            DbCommand command = this.database.GetStoredProcCommand(procedureName);
            command.CommandTimeout = DataAccessConfigurationView.GetCommondTimeout(dataAccessSettings);
            this.AssignParameterValues(command, parameters);
            this.Fill(dataTable, command);
        }

        /// <summary>
        /// Execute the stored procedure, and retrieve records to fill the input DataSet.
        /// </summary>
        /// <param name="dataTable">The DataTable need to be filled</param>
        /// <param name="columnMapping">The column in datatable mapping to databse table column</param>
        /// <param name="destinationTable">The table which you will insert data</param>
        /// <param name="commandTimeOut">Command time out data </param>
        /// <param name="bulkCopyOption">The type of cope option</param>
        public void ExecuteBulkCopy(DataTable dataTable, string destinationTable,int commandTimeOut, Dictionary<string, string> columnMapping=null,SqlBulkCopyOptions bulkCopyOption = SqlBulkCopyOptions.Default)
        {
            string destinationColumn;

            if (dataTable != null && dataTable.Rows.Count != 0)
            {
                using (DbConnection connection = database.CreateConnection())
                {
                    connection.Open();

                    SqlConnection sqlConnect = connection as SqlConnection;

                    if (bulkCopyOption == SqlBulkCopyOptions.UseInternalTransaction)
                    {
                        //fix dotTest code scan P2 issue by jing sheng
                        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnect, bulkCopyOption, null))
                        {

                            if (columnMapping != null)
                            {
                                foreach (string sourceColumn in columnMapping.Keys)
                                {
                                    if (columnMapping.TryGetValue(sourceColumn, out destinationColumn))
                                    {
                                        SqlBulkCopyColumnMapping mapping = new SqlBulkCopyColumnMapping(sourceColumn, destinationColumn);
                                        bulkCopy.ColumnMappings.Add(mapping);
                                    }
                                }
                            }

                            bulkCopy.BatchSize = dataTable.Rows.Count;
                            bulkCopy.BulkCopyTimeout = commandTimeOut;
                            bulkCopy.DestinationTableName = destinationTable;
                            bulkCopy.WriteToServer(dataTable);
                        }
                    }
                    else
                    {

                        using (SqlTransaction transaction = sqlConnect.BeginTransaction())
                        using (SqlBulkCopy bulkCope = new SqlBulkCopy(sqlConnect, bulkCopyOption, transaction))
                        {
                            if (columnMapping != null)
                            {
                                foreach (string sourceColumn in columnMapping.Keys)
                                {
                                    if (columnMapping.TryGetValue(sourceColumn, out destinationColumn))
                                    {
                                        SqlBulkCopyColumnMapping mapping = new SqlBulkCopyColumnMapping(sourceColumn, destinationColumn);
                                        bulkCope.ColumnMappings.Add(mapping);
                                    }
                                }
                            }

                            bulkCope.BatchSize = dataTable.Rows.Count;
                            bulkCope.BulkCopyTimeout = commandTimeOut;
                            bulkCope.DestinationTableName = destinationTable;
                            try
                            {
                                bulkCope.WriteToServer(dataTable);
                                transaction.Commit();
                            }
                            catch
                            {
                                transaction.Rollback();
                                throw;
                            }
                        }
                    }
                    //dotTest fix : Closing action should be proceeded at first place in finally statement.
                    //connection.Close();
                }
            }
        }


        #endregion

    }
}